package classes;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class GroupManager {

	static Statement statement;
	private static final String groupTable = "groups";
	public static String GROUPMANAGER = "groupManager";
	private static final String studentGroupTable = "group_vs_student";

	public GroupManager(Statement statement) {
		this.statement = statement;
	}

	/**
	 * 
	 * @param groupName
	 * @param lecturerId
	 * @param subId
	 * @return boolean
	 */
	public boolean createGroup(String groupName, Integer lecturerId,
			Integer subId) {
		try {
			statement.executeUpdate("Insert into " + groupTable + " values("
					+ "\'" + groupName + "\'," + "null," + lecturerId + ","
					+ subId + ");");
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 
	 * @param group_id
	 * @return
	 */

	public int getGroupId(String group_name, int subject_id) {
		try {
			ResultSet rs = statement.executeQuery("Select group_id from "
					+ groupTable + " where group_name='" + group_name
					+ "\' and subject_id=" + subject_id + ";");
			if (rs.next())
				return rs.getInt("group_id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public void deleteStudentFromGroup(int student_id, int group_id) {
		try {
			statement.executeUpdate("delete from " + studentGroupTable
					+ " where student_id=" + student_id + " and group_id="
					+ group_id + ";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public ArrayList<String> getGroupNames(int subject_id, int lecturer_id) {
		ArrayList<String> arr = new ArrayList<String>();
		try {
			ResultSet rs = statement.executeQuery("Select group_name from "
					+ groupTable + " where subject_id=" + subject_id
					+ " and + lecturer_id=" + lecturer_id);
			while (rs.next()) {
				arr.add(rs.getString("group_name"));
			}
			return arr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public void addStudentToGroup(int student_id, int group_id) {
		try {
			statement.executeUpdate("Insert into " + studentGroupTable
					+ " values(" + student_id + "," + group_id + ");");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static ArrayList<Integer> getStundetsOfGroup(int group_id) {
		ArrayList<Integer> arr = new ArrayList<Integer>();
		try {
			ResultSet rs = statement.executeQuery("Select student_id from "
					+ studentGroupTable + " where group_id=" + group_id + ";");
			while (rs.next()) {
				arr.add(rs.getInt("student_id"));
			}
			return arr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arr;
	}

	public void renameGroup(int group_id, String group_name) {
		try {
			statement.executeUpdate("Update " + groupTable
					+ " set group_name=\'" + group_name + "\' where group_id='"
					+ group_id + "';");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteGroup(int group_id) {
		try {
			statement.executeUpdate("delete from " + groupTable
					+ " where group_id=" + group_id + ";");
			statement.executeUpdate("delete from " + studentGroupTable
					+ " where group_id=" + group_id + ";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Group getGroup(int group_id) {
		try {

			ResultSet set1 = statement.executeQuery("Select * from "
					+ groupTable + " where group_id=" + group_id);
			set1.next();
			String groupName = set1.getString("group_name");
			int lecturerId = set1.getInt("lecturer_id");
			int subId = set1.getInt("subject_id");
			ResultSet set2 = statement
					.executeQuery("Select subject_name from subjects where subject_id = "
							+ subId);
			set2.next();
			String subjectName = set2.getString("Subject_name");
			ResultSet set3 = statement
					.executeQuery("Select name,surname from users where user_id = "
							+ lecturerId);
			set3.next();
			String name = set3.getString("name");
			name += set3.getString("surname");
			Group group = new Group(group_id, groupName, lecturerId, name,
					subId, subjectName, null);
			return group;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
